package com.flyco.modules.util;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.scripting.xmltags.OgnlCache;
import org.apache.ibatis.scripting.xmltags.XMLScriptBuilder;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;

/**
 * 指定要执行的sql语句，参数变量，数据源，返回查询结果
 * 功能：
 * 1.支持动态参数
 * 2.防止sql注入
 * 如果没有动态参数，只是单纯的查询，可以自己写如下内容：
 *      <select id="executeSql" resultType="java.util.HashMap">
 *         ${sql}
 *     </select>
 *     然后数据库配置sql内容，直接sql注入并执行即可。该方法通过传入Function，来回调执行这个方法
 */
@Slf4j
@Service
public class SqlExecService {
    private final static String sqlNameSpace = SqlExecService.class.getName();
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    @Autowired
    private DynamicRoutingDataSource dynamicRoutingDataSource;

    public List<Map> select(String sql, Map<String, Object> params, String ds, Function<String,List<Map>> function){
        log.info("sql={},params={},ds={}",sql,params,ds);
        MappedStatement statement = createMappedStatement(sql);
        BoundSql boundSql = statement.getBoundSql(params);
        // 没有#{} 参数， 走静态sql，回调function方法
        if (boundSql.getParameterMappings().size()==0){
            return function.apply(boundSql.getSql());
        }

        List result = new ArrayList();
        DataSource dataSource = dynamicRoutingDataSource.getDataSource(ds);
        try (Connection conn = dataSource.getConnection()){
            try (PreparedStatement statement1 = conn.prepareStatement(boundSql.getSql())){
                for (int i=0; i<boundSql.getParameterMappings().size(); i++){
                    statement1.setObject(i+1, OgnlCache.getValue(boundSql.getParameterMappings().get(i).getProperty(), params));
                }
                try (ResultSet rs = statement1.executeQuery()){
                    while (rs.next()){
                        Map<String, Object> row = new HashMap<>();
                        for (int i=0, size=rs.getMetaData().getColumnCount(); i<size; i++){
                            row.put(rs.getMetaData().getColumnName(i+1), rs.getObject(i+1));
                        }
                        result.add(row);
                    }
                }
            }
        }catch(Exception e){
            log.error("select执行报错：", e);
            return null;
        }
        return result;
    }

    MappedStatement createMappedStatement(String sql){
        Configuration mapperConfig = sqlSessionFactory.getConfiguration();
        String contextXml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                "<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\"  \n" +
                "\"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n" +
                "<mapper namespace=\"" + sqlNameSpace + "\">\n" +
                "\t<select id=\"executeSql\" resultType=\"java.util.HashMap\">\n" +
                sql +
                "\t</select>\n" +
                "</mapper>\n";
        List<XNode> nodes = new XPathParser(new StringReader(contextXml), true, mapperConfig.getVariables(), new XMLMapperEntityResolver()).evalNodes("/mapper/select");
        SqlSource sqlSource = new XMLScriptBuilder(mapperConfig, nodes.get(0)).parseScriptNode();
        return new MappedStatement.Builder(mapperConfig, sqlNameSpace + ".executeSql",sqlSource, SqlCommandType.SELECT ).lang(mapperConfig.getDefaultScriptingLanguageInstance()).build();
    }

}
